{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Which college district has the fewest low-income families?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A pilot program was run by a local cable operator in the county to provide low-cost computers and Internet access to low-income families with kids in high school. This showed a marked improvement in school performance for these kids, and the program has brought the company a fair amount of positive publicity and goodwill in the community.\n",
    "\n",
    "Company officials now want to set up a similar program for community college students. The company provides Internet access to the five community college districts in the county, and officials are aware that the colleges are under a lot of pressure - they are facing funding cuts at the same time as increased demand for enrollment. To try to improve the situation the colleges are turning more and more to distance learning, primarily via the Internet. By providing computers and Internet access, the cable company can enable more low-income students to take advantage of online classes.\n",
    "\n",
    "This case study uses ArcGIS API for Python to find districts that have the fewest low income families in order to empower these students.\n",
    "\n",
    "We will use ``summarize_within`` tool to get the number of low-income families within each community district. We will also visualize this using the map widget."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Connect-to-your-ArcGIS-Online-organization\" data-toc-modified-id=\"Connect-to-your-ArcGIS-Online-organization-2\">Connect to your ArcGIS Online organization</a></span></li><li><span><a href=\"#Get-data-for-analysis\" data-toc-modified-id=\"Get-data-for-analysis-3\">Get data for analysis</a></span></li><li><span><a href=\"#Find-the-community-college-district-with-the-fewest-low-income-families\" data-toc-modified-id=\"Find-the-community-college-district-with-the-fewest-low-income-families-4\">Find the community college district with the fewest low-income families</a></span></li><li><span><a href=\"#Get-the-number-of-low-income-households-in-each-district\" data-toc-modified-id=\"Get-the-number-of-low-income-households-in-each-district-5\">Get the number of low-income households in each district</a></span></li><li><span><a href=\"#Visualization-to-show-district-with-fewest-households\" data-toc-modified-id=\"Visualization-to-show-district-with-fewest-households-6\">Visualization to show district with fewest households</a></span></li><li><span><a href=\"#Conclusion\" data-toc-modified-id=\"Conclusion-7\">Conclusion</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to your ArcGIS Online organization\n",
    "\n",
    "\n",
    "We first establish a connection to our organization which could be an ArcGIS Online organization or an ArcGIS Enterprise. To be able to run the code using ArcGIS API for Python, we will need to provide credentials of a user within an ArcGIS Online organization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from arcgis.gis import GIS\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please sign-in into your organization to continue to execute this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "gis = GIS('home')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get data for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "san_diego_data = gis.content.search('title:CommunityCollege_CensusTracts owner:api_data_owner', \n",
    "                                 'Feature layer',\n",
    "                                  outside_org=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Item title:\"CommunityCollege_CensusTracts\" type:Feature Layer Collection owner:api_data_owner>]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "san_diego_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=a84a901410784c0a8dd4532acab38025' target='_blank'>\n",
       "                        <img src='' width='200' height='133' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=a84a901410784c0a8dd4532acab38025' target='_blank'><b>CommunityCollege_CensusTracts</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://geosaurus.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\" width=16 height=16>Feature Layer Collection by api_data_owner\n",
       "                        <br/>Last Modified: April 11, 2020\n",
       "                        <br/>0 comments, 89 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"CommunityCollege_CensusTracts\" type:Feature Layer Collection owner:api_data_owner>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display\n",
    "\n",
    "for item in san_diego_data:\n",
    "    display(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "san_diego_item = san_diego_data[0] # get first item from the list of items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "census_tract_income\n",
      "Community_College_Dist\n"
     ]
    }
   ],
   "source": [
    "for lyr in san_diego_item.layers:\n",
    "    print(lyr.properties.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the item is a Feature Layer Collection, accessing the layers property will give us a list of Feature Layers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_tract_income = san_diego_item.layers[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "community_college_dist = san_diego_item.layers[1] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=></img>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m1 = gis.map('San Diego')\n",
    "m1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "m1.add_layer(community_college_dist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=></img>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m2 = gis.map('San Diego')\n",
    "m2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find the community college district with the fewest low income families"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the layer into pandas dataframe to calculate the number of households in each tract with income less than $30,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sdf = pd.DataFrame.spatial.from_layer(census_tract_income)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['FID', 'TRACT', 'INCOME_ALL', 'INCOME_LES', 'INCOME_10K', 'INCOME_15K',\n",
       "       'INCOME_20K', 'INCOME_25K', 'INCOME_30K', 'INCOME_35K', 'INCOME_40K',\n",
       "       'INCOME_45K', 'INCOME_50K', 'INCOME_60K', 'INCOME_75K', 'INCOME_100',\n",
       "       'INCOME_125', 'INCOME_150', 'INCOME_200', 'Shape__Area',\n",
       "       'Shape__Length', 'SHAPE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FID</th>\n",
       "      <th>TRACT</th>\n",
       "      <th>INCOME_ALL</th>\n",
       "      <th>INCOME_LES</th>\n",
       "      <th>INCOME_10K</th>\n",
       "      <th>INCOME_15K</th>\n",
       "      <th>INCOME_20K</th>\n",
       "      <th>INCOME_25K</th>\n",
       "      <th>INCOME_30K</th>\n",
       "      <th>INCOME_35K</th>\n",
       "      <th>...</th>\n",
       "      <th>INCOME_50K</th>\n",
       "      <th>INCOME_60K</th>\n",
       "      <th>INCOME_75K</th>\n",
       "      <th>INCOME_100</th>\n",
       "      <th>INCOME_125</th>\n",
       "      <th>INCOME_150</th>\n",
       "      <th>INCOME_200</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>SHAPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>7700</td>\n",
       "      <td>4148</td>\n",
       "      <td>243</td>\n",
       "      <td>205</td>\n",
       "      <td>158</td>\n",
       "      <td>195</td>\n",
       "      <td>229</td>\n",
       "      <td>279</td>\n",
       "      <td>278</td>\n",
       "      <td>...</td>\n",
       "      <td>445</td>\n",
       "      <td>526</td>\n",
       "      <td>370</td>\n",
       "      <td>379</td>\n",
       "      <td>73</td>\n",
       "      <td>127</td>\n",
       "      <td>125</td>\n",
       "      <td>1724049.019531</td>\n",
       "      <td>6919.424522</td>\n",
       "      <td>{\"rings\": [[[-13051046.6746253, 3866695.333166...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>7800</td>\n",
       "      <td>2510</td>\n",
       "      <td>294</td>\n",
       "      <td>132</td>\n",
       "      <td>180</td>\n",
       "      <td>160</td>\n",
       "      <td>135</td>\n",
       "      <td>250</td>\n",
       "      <td>116</td>\n",
       "      <td>...</td>\n",
       "      <td>280</td>\n",
       "      <td>263</td>\n",
       "      <td>178</td>\n",
       "      <td>107</td>\n",
       "      <td>64</td>\n",
       "      <td>52</td>\n",
       "      <td>9</td>\n",
       "      <td>2889814.199219</td>\n",
       "      <td>11223.567885</td>\n",
       "      <td>{\"rings\": [[[-13049196.649225, 3869830.7042951...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7901</td>\n",
       "      <td>2953</td>\n",
       "      <td>240</td>\n",
       "      <td>156</td>\n",
       "      <td>154</td>\n",
       "      <td>191</td>\n",
       "      <td>209</td>\n",
       "      <td>233</td>\n",
       "      <td>168</td>\n",
       "      <td>...</td>\n",
       "      <td>325</td>\n",
       "      <td>393</td>\n",
       "      <td>233</td>\n",
       "      <td>150</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "      <td>1785775.15625</td>\n",
       "      <td>5749.634908</td>\n",
       "      <td>{\"rings\": [[[-13051806.5792234, 3868598.509832...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>7903</td>\n",
       "      <td>2429</td>\n",
       "      <td>154</td>\n",
       "      <td>163</td>\n",
       "      <td>184</td>\n",
       "      <td>174</td>\n",
       "      <td>171</td>\n",
       "      <td>139</td>\n",
       "      <td>195</td>\n",
       "      <td>...</td>\n",
       "      <td>288</td>\n",
       "      <td>145</td>\n",
       "      <td>310</td>\n",
       "      <td>124</td>\n",
       "      <td>30</td>\n",
       "      <td>43</td>\n",
       "      <td>19</td>\n",
       "      <td>1075470.988281</td>\n",
       "      <td>4651.499315</td>\n",
       "      <td>{\"rings\": [[[-13050375.5212048, 3868973.977334...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>7904</td>\n",
       "      <td>3157</td>\n",
       "      <td>335</td>\n",
       "      <td>219</td>\n",
       "      <td>187</td>\n",
       "      <td>208</td>\n",
       "      <td>218</td>\n",
       "      <td>199</td>\n",
       "      <td>188</td>\n",
       "      <td>...</td>\n",
       "      <td>304</td>\n",
       "      <td>316</td>\n",
       "      <td>326</td>\n",
       "      <td>162</td>\n",
       "      <td>53</td>\n",
       "      <td>67</td>\n",
       "      <td>19</td>\n",
       "      <td>1318393.753906</td>\n",
       "      <td>4961.527797</td>\n",
       "      <td>{\"rings\": [[[-13050786.6266337, 3868042.625540...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   FID  TRACT  INCOME_ALL  INCOME_LES  INCOME_10K  INCOME_15K  INCOME_20K   \n",
       "0    1   7700        4148         243         205         158         195  \\\n",
       "1    2   7800        2510         294         132         180         160   \n",
       "2    3   7901        2953         240         156         154         191   \n",
       "3    4   7903        2429         154         163         184         174   \n",
       "4    5   7904        3157         335         219         187         208   \n",
       "\n",
       "   INCOME_25K  INCOME_30K  INCOME_35K  ...  INCOME_50K  INCOME_60K   \n",
       "0         229         279         278  ...         445         526  \\\n",
       "1         135         250         116  ...         280         263   \n",
       "2         209         233         168  ...         325         393   \n",
       "3         171         139         195  ...         288         145   \n",
       "4         218         199         188  ...         304         316   \n",
       "\n",
       "   INCOME_75K  INCOME_100  INCOME_125  INCOME_150  INCOME_200     Shape__Area   \n",
       "0         370         379          73         127         125  1724049.019531  \\\n",
       "1         178         107          64          52           9  2889814.199219   \n",
       "2         233         150          49          42          25   1785775.15625   \n",
       "3         310         124          30          43          19  1075470.988281   \n",
       "4         326         162          53          67          19  1318393.753906   \n",
       "\n",
       "   Shape__Length                                              SHAPE  \n",
       "0    6919.424522  {\"rings\": [[[-13051046.6746253, 3866695.333166...  \n",
       "1   11223.567885  {\"rings\": [[[-13049196.649225, 3869830.7042951...  \n",
       "2    5749.634908  {\"rings\": [[[-13051806.5792234, 3868598.509832...  \n",
       "3    4651.499315  {\"rings\": [[[-13050375.5212048, 3868973.977334...  \n",
       "4    4961.527797  {\"rings\": [[[-13050786.6266337, 3868042.625540...  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The census tract layer contains the number of households in each of several income categories, such as less than \\$10,000, \\$10,000 to \\$15,000, \\$15,000 to \\$20,000, and so on.\n",
    "\n",
    "The aim of the project is to provide support to families with an annual income less than \\$30,000.\n",
    "\n",
    "We will add a field to the census tract dataframe and sum the number of households in each tract with income less than \\$30,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf['income_lt_30k'] = sdf['INCOME_LES'] + sdf['INCOME_10K'] + sdf['INCOME_15K'] + sdf['INCOME_20K'] + sdf['INCOME_25K']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1030\n",
       "1     901\n",
       "2     950\n",
       "3     846\n",
       "4    1167\n",
       "Name: income_lt_30k, dtype: Int32"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.income_lt_30k.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FID</th>\n",
       "      <th>TRACT</th>\n",
       "      <th>INCOME_ALL</th>\n",
       "      <th>INCOME_LES</th>\n",
       "      <th>INCOME_10K</th>\n",
       "      <th>INCOME_15K</th>\n",
       "      <th>INCOME_20K</th>\n",
       "      <th>INCOME_25K</th>\n",
       "      <th>INCOME_30K</th>\n",
       "      <th>INCOME_35K</th>\n",
       "      <th>...</th>\n",
       "      <th>INCOME_60K</th>\n",
       "      <th>INCOME_75K</th>\n",
       "      <th>INCOME_100</th>\n",
       "      <th>INCOME_125</th>\n",
       "      <th>INCOME_150</th>\n",
       "      <th>INCOME_200</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>income_lt_30k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>7700</td>\n",
       "      <td>4148</td>\n",
       "      <td>243</td>\n",
       "      <td>205</td>\n",
       "      <td>158</td>\n",
       "      <td>195</td>\n",
       "      <td>229</td>\n",
       "      <td>279</td>\n",
       "      <td>278</td>\n",
       "      <td>...</td>\n",
       "      <td>526</td>\n",
       "      <td>370</td>\n",
       "      <td>379</td>\n",
       "      <td>73</td>\n",
       "      <td>127</td>\n",
       "      <td>125</td>\n",
       "      <td>1724049.019531</td>\n",
       "      <td>6919.424522</td>\n",
       "      <td>{\"rings\": [[[-13051046.6746253, 3866695.333166...</td>\n",
       "      <td>1030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>7800</td>\n",
       "      <td>2510</td>\n",
       "      <td>294</td>\n",
       "      <td>132</td>\n",
       "      <td>180</td>\n",
       "      <td>160</td>\n",
       "      <td>135</td>\n",
       "      <td>250</td>\n",
       "      <td>116</td>\n",
       "      <td>...</td>\n",
       "      <td>263</td>\n",
       "      <td>178</td>\n",
       "      <td>107</td>\n",
       "      <td>64</td>\n",
       "      <td>52</td>\n",
       "      <td>9</td>\n",
       "      <td>2889814.199219</td>\n",
       "      <td>11223.567885</td>\n",
       "      <td>{\"rings\": [[[-13049196.649225, 3869830.7042951...</td>\n",
       "      <td>901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7901</td>\n",
       "      <td>2953</td>\n",
       "      <td>240</td>\n",
       "      <td>156</td>\n",
       "      <td>154</td>\n",
       "      <td>191</td>\n",
       "      <td>209</td>\n",
       "      <td>233</td>\n",
       "      <td>168</td>\n",
       "      <td>...</td>\n",
       "      <td>393</td>\n",
       "      <td>233</td>\n",
       "      <td>150</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "      <td>25</td>\n",
       "      <td>1785775.15625</td>\n",
       "      <td>5749.634908</td>\n",
       "      <td>{\"rings\": [[[-13051806.5792234, 3868598.509832...</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>7903</td>\n",
       "      <td>2429</td>\n",
       "      <td>154</td>\n",
       "      <td>163</td>\n",
       "      <td>184</td>\n",
       "      <td>174</td>\n",
       "      <td>171</td>\n",
       "      <td>139</td>\n",
       "      <td>195</td>\n",
       "      <td>...</td>\n",
       "      <td>145</td>\n",
       "      <td>310</td>\n",
       "      <td>124</td>\n",
       "      <td>30</td>\n",
       "      <td>43</td>\n",
       "      <td>19</td>\n",
       "      <td>1075470.988281</td>\n",
       "      <td>4651.499315</td>\n",
       "      <td>{\"rings\": [[[-13050375.5212048, 3868973.977334...</td>\n",
       "      <td>846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>7904</td>\n",
       "      <td>3157</td>\n",
       "      <td>335</td>\n",
       "      <td>219</td>\n",
       "      <td>187</td>\n",
       "      <td>208</td>\n",
       "      <td>218</td>\n",
       "      <td>199</td>\n",
       "      <td>188</td>\n",
       "      <td>...</td>\n",
       "      <td>316</td>\n",
       "      <td>326</td>\n",
       "      <td>162</td>\n",
       "      <td>53</td>\n",
       "      <td>67</td>\n",
       "      <td>19</td>\n",
       "      <td>1318393.753906</td>\n",
       "      <td>4961.527797</td>\n",
       "      <td>{\"rings\": [[[-13050786.6266337, 3868042.625540...</td>\n",
       "      <td>1167</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   FID  TRACT  INCOME_ALL  INCOME_LES  INCOME_10K  INCOME_15K  INCOME_20K   \n",
       "0    1   7700        4148         243         205         158         195  \\\n",
       "1    2   7800        2510         294         132         180         160   \n",
       "2    3   7901        2953         240         156         154         191   \n",
       "3    4   7903        2429         154         163         184         174   \n",
       "4    5   7904        3157         335         219         187         208   \n",
       "\n",
       "   INCOME_25K  INCOME_30K  INCOME_35K  ...  INCOME_60K  INCOME_75K   \n",
       "0         229         279         278  ...         526         370  \\\n",
       "1         135         250         116  ...         263         178   \n",
       "2         209         233         168  ...         393         233   \n",
       "3         171         139         195  ...         145         310   \n",
       "4         218         199         188  ...         316         326   \n",
       "\n",
       "   INCOME_100  INCOME_125  INCOME_150  INCOME_200     Shape__Area   \n",
       "0         379          73         127         125  1724049.019531  \\\n",
       "1         107          64          52           9  2889814.199219   \n",
       "2         150          49          42          25   1785775.15625   \n",
       "3         124          30          43          19  1075470.988281   \n",
       "4         162          53          67          19  1318393.753906   \n",
       "\n",
       "   Shape__Length                                              SHAPE   \n",
       "0    6919.424522  {\"rings\": [[[-13051046.6746253, 3866695.333166...  \\\n",
       "1   11223.567885  {\"rings\": [[[-13049196.649225, 3869830.7042951...   \n",
       "2    5749.634908  {\"rings\": [[[-13051806.5792234, 3868598.509832...   \n",
       "3    4651.499315  {\"rings\": [[[-13050375.5212048, 3868973.977334...   \n",
       "4    4961.527797  {\"rings\": [[[-13050786.6266337, 3868042.625540...   \n",
       "\n",
       "   income_lt_30k  \n",
       "0           1030  \n",
       "1            901  \n",
       "2            950  \n",
       "3            846  \n",
       "4           1167  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(605, 23)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will import the spatially enabled dataframe back into the GIS and create a feature layer. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "census_tract = gis.content.import_data(sdf,\n",
    "                                       title='CensusTract',\n",
    "                                       tags='datascience')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=ede138385509430eb45ec5d0ac0c569c' target='_blank'>\n",
       "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=ede138385509430eb45ec5d0ac0c569c' target='_blank'><b>CensusTract</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://geosaurus.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by arcgis_python\n",
       "                        <br/>Last Modified: April 18, 2023\n",
       "                        <br/>0 comments, 0 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"CensusTract\" type:Feature Layer Collection owner:arcgis_python>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_tract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the number of low-income households in each district"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will summarize census tracts by community college districts to find the total number of low-income households in each district. If a tract falls in two or more districts, the value for that tract will be split proportionally between the districts (based on the area of the tract in each district)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "from arcgis.features.summarize_data import summarize_within\n",
    "from datetime import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "{\"cost\": 0.61}\n"
     ]
    }
   ],
   "source": [
    "tracts_within_boundary = summarize_within(community_college_dist,\n",
    "                                          census_tract,\n",
    "                                          summary_fields=[\"income_lt_ SUM\"],\n",
    "                                          shape_units='SquareMiles',\n",
    "                                          output_name='TractsWithinBoundary' + str(dt.now().microsecond))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=5eb5139f01724c4394257ea83f53bafd' target='_blank'>\n",
       "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://geosaurus.maps.arcgis.com/home/item.html?id=5eb5139f01724c4394257ea83f53bafd' target='_blank'><b>TractsWithinBoundary560119</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://geosaurus.maps.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\" width=16 height=16>Feature Layer Collection by jyaist_geosaurus\n",
       "                        <br/>Last Modified: July 27, 2023\n",
       "                        <br/>0 comments, 0 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"TractsWithinBoundary560119\" type:Feature Layer Collection owner:jyaist_geosaurus>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tracts_within_boundary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=></img>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m3 = gis.map('San Diego')\n",
    "m3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "m3.add_layer(tracts_within_boundary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The map displays the census tracts color-coded by the number of households in each census tract with income less than $30,000 per year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "tracts_within_boundary_lyr = tracts_within_boundary.layers[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf = pd.DataFrame.spatial.from_layer(tracts_within_boundary_lyr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['OBJECTID_1', 'OBJECTID', 'DISTRICT', 'Shape_Leng', 'sum_income_lt_',\n",
       "       'sum_Area_SquareMiles', 'Polygon_Count', 'AnalysisArea', 'SHAPE'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf.sort_values(['sum_income_lt_'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>OBJECTID_1</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>DISTRICT</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>sum_income_lt_</th>\n",
       "      <th>sum_Area_SquareMiles</th>\n",
       "      <th>Polygon_Count</th>\n",
       "      <th>AnalysisArea</th>\n",
       "      <th>SHAPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>MIRA COSTA COMMUNITY COLLEGE</td>\n",
       "      <td>529254.242323</td>\n",
       "      <td>28286.961822</td>\n",
       "      <td>179.904967</td>\n",
       "      <td>87</td>\n",
       "      <td>180.057307</td>\n",
       "      <td>{\"rings\": [[[-13069560.2323, 3941041.6565], [-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>SOUTHWESTERN COMMUNITY COLLEGE</td>\n",
       "      <td>484545.196366</td>\n",
       "      <td>40860.319742</td>\n",
       "      <td>171.085801</td>\n",
       "      <td>111</td>\n",
       "      <td>171.34353</td>\n",
       "      <td>{\"rings\": [[[-13045570.4191, 3857253.9374], [-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>GROSSMONT-CUYAMACA COMMUNITY COLLEGE</td>\n",
       "      <td>962386.012704</td>\n",
       "      <td>48778.127326</td>\n",
       "      <td>1137.093733</td>\n",
       "      <td>118</td>\n",
       "      <td>1137.329793</td>\n",
       "      <td>{\"rings\": [[[-13000869.2404, 3890488.4302], [-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>PALOMAR COMMUNITY COLLEGE</td>\n",
       "      <td>1538204.61099</td>\n",
       "      <td>56548.178816</td>\n",
       "      <td>2554.695555</td>\n",
       "      <td>152</td>\n",
       "      <td>2554.78782</td>\n",
       "      <td>{\"rings\": [[[-13078663.7712, 3962536.5573], [-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>SAN DIEGO COMMUNITY COLLEGE</td>\n",
       "      <td>608636.549263</td>\n",
       "      <td>127840.876015</td>\n",
       "      <td>217.566587</td>\n",
       "      <td>253</td>\n",
       "      <td>217.5859</td>\n",
       "      <td>{\"rings\": [[[-13039483.6168, 3888486.6244], [-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID_1  OBJECTID                              DISTRICT     Shape_Leng   \n",
       "1           2         3          MIRA COSTA COMMUNITY COLLEGE  529254.242323  \\\n",
       "0           1         5        SOUTHWESTERN COMMUNITY COLLEGE  484545.196366   \n",
       "3           4         1  GROSSMONT-CUYAMACA COMMUNITY COLLEGE  962386.012704   \n",
       "4           5         2             PALOMAR COMMUNITY COLLEGE  1538204.61099   \n",
       "2           3         4           SAN DIEGO COMMUNITY COLLEGE  608636.549263   \n",
       "\n",
       "   sum_income_lt_  sum_Area_SquareMiles  Polygon_Count  AnalysisArea   \n",
       "1    28286.961822            179.904967             87    180.057307  \\\n",
       "0    40860.319742            171.085801            111     171.34353   \n",
       "3    48778.127326           1137.093733            118   1137.329793   \n",
       "4    56548.178816           2554.695555            152    2554.78782   \n",
       "2   127840.876015            217.566587            253      217.5859   \n",
       "\n",
       "                                               SHAPE  \n",
       "1  {\"rings\": [[[-13069560.2323, 3941041.6565], [-...  \n",
       "0  {\"rings\": [[[-13045570.4191, 3857253.9374], [-...  \n",
       "3  {\"rings\": [[[-13000869.2404, 3890488.4302], [-...  \n",
       "4  {\"rings\": [[[-13078663.7712, 3962536.5573], [-...  \n",
       "2  {\"rings\": [[[-13039483.6168, 3888486.6244], [-...  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualization to show district with fewest households"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=></img>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m4 = gis.map('San Diego')\n",
    "m4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "m4.add_layer(tracts_within_boundary, {\"renderer\":\"ClassedSizeRenderer\",\n",
    "                                      \"field_name\": \"sum_income_lt_\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's clear that the Mira Costa district has by far the fewest low-income households. That's where the pilot program could be set up."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have successfully located a district with the fewest low income families.\n",
    "We can assess the success of the project for the next 6 months and give recommendations to expand the program across other areas in the country."
   ]
  }
 ],
 "metadata": {
  "esriNotebookRuntime": {
   "notebookRuntimeName": "ArcGIS Notebook Python 3 Standard",
   "notebookRuntimeVersion": "9.0"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
